package com.iris.live.services.data.repositories.impl;

import com.iris.live.services.data.models.TSalesAdminCheckModel;
import com.iris.live.services.data.models.用户Model;
import com.iris.live.services.data.repositories.SalesAdminCheckRepositorySql;
import com.iris.live.services.models.PageData;
import com.iris.live.services.models.RequestBase;
import com.iris.live.services.services.DataCheckService;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by DF on 5/20/2017
 */
public class SalesAdminCheckRepositoryImpl extends BaseRepository implements SalesAdminCheckRepositorySql {
    @Override
    public List getSaAdSysDataByPermitAndDate(String permit) {
        String sql = " select  \n" +
                "   b.车架号 as vin,  \n" +
                "   b.入库日期 as in_stock_dt,  \n" +
                "   b.出库日期 as out_stock_dt,  \n" +
                "   (select 上报厂家日期 from 库存利润表 where 库存编号 = b.库存编号) as re_dt,   \n" +
                "   (select 订单客户姓名 from 报价订单表 where 订单编号 = a.订单编号) as order_cus_name,  \n" +
                "   c.开票客户姓名 as invoice_uname,  \n" +
                "   a.折后价 as price_ti,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '保险') as freeby_in,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '贴息') as freeby_sub_int,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = 'A保') as freeby_a,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = 'B保') as freeby_b,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '尚银卡') as freeby_si,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '逸蓝卡') as freeby_bl,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '购物卡') as freeby_sh,  \n" +
                "   (select cast(随车成本 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '加油卡') as freeby_oi,  \n" +
                // 随车成本用车型关联很难取，因为1.随车编号可能对应的是品牌，2.品牌设置了成本，车系也设置了成本，这时还要取车系的
//                 "   (select siCost from dealerAttachmentPolicy where permit = a.许可 and car = b.车型编号 and status = 1 and department = '销售部' and item = '加油卡' and startDate <= current_date() and current_date() <= endDate ) as freeby_oi,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '保险' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_in,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = 'SSSC' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_sssc,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = 'SD卡' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_sd,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '加油卡' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_oil,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '实物礼品' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_pre,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '服务礼券' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_ti,  \n" +
                "   if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '购置税' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as pur_tax_si,  \n" +
                "   (SELECT IFNULL(a.BMBS_retail_SI, 0.00)   \n" +
                "   FROM snp_setting a     \n" +
                "   WHERE a.carCode in (    \n" +
                " 	select 车型编号    \n" +
                " 	from lsh车型    \n" +
                " 	where (来源, 品牌, 级别, 车型, 车型详细) in (    \n" +
                " 		select 来源, 品牌, 级别, 车型, 车型详细     \n" +
                " 		from lsh车型     \n" +
                " 		where 车型编号 = (select 车型编号 from lsh库存表 where 库存编号 = b.库存编号)    \n" +
                " 	)    \n" +
                "   )     \n" +
                "   and a.permit = b.所属经销商    \n" +
                "   and a.y_m = '190001'     \n" +
                "   AND (a.apperanceCode in (select 颜色代码 FROM lsh库存表 where 库存编号 = b.库存编号) or a.apperanceCode = 0)     \n" +
                "   ORDER BY a.apperanceCode DESC     \n" +
                "   LIMIT 1    \n" +
                "   ) as re_si,  \n" +
                "   (SELECT if(b.所属经销商 = b.调入经销商, 0.00, IFNULL(a.BMBS_wholesale_SI, 0.00))   \n" +
                "   FROM snp_setting a     \n" +
                "   WHERE a.carCode in (    \n" +
                " 	select 车型编号    \n" +
                " 	from lsh车型    \n" +
                " 	where (来源, 品牌, 级别, 车型, 车型详细) in (    \n" +
                " 		select 来源, 品牌, 级别, 车型, 车型详细     \n" +
                " 		from lsh车型     \n" +
                " 		where 车型编号 = (select 车型编号 from lsh库存表 where 库存编号 = b.库存编号)    \n" +
                " 	)    \n" +
                "   )     \n" +
                "   and a.permit = b.所属经销商    \n" +
                "   and a.y_m = '190001'     \n" +
                "   AND (a.apperanceCode in (select 颜色代码 FROM lsh库存表 where 库存编号 = b.库存编号) or a.apperanceCode = 0)     \n" +
                "   ORDER BY a.apperanceCode DESC     \n" +
                "   LIMIT 1    \n" +
                "   ) as wh_si  \n" +
                " from 报价单表 a,  \n" +
                "   vw_max_quoted_code ag,  \n" +
                "   lsh库存表 b,  \n" +
                "   报价交车表 c,  \n" +
                "   virtual_stock d  \n" +
                " where a.交车 = c.编号  \n" +
                " and a.报价编号 = ag.报价编号  \n" +
                " and a.库存编号 = b.库存编号  \n" +
                " and not((b.车架号 like concat('%',d.value,'%')))  \n" +
                " and b.车辆零售状态 = '已卖'  \n" +
                " and ((date_format(c.开票时间, '%Y-%m-%d') <= current_date() AND date_format(c.开票时间, '%Y-%m-%d') >= date_format(concat(year(current_date()), '-', month(current_date()), '-01'), '%Y-%m-%d')) \n" +
                "                       and (c.退票时间 is null or date_format(c.退票时间, '%Y-%m-%d') < date_format(c.开票时间, '%Y-%m-%d')))   \n" +
                " and a.许可 = :permit ";
        Map<String, Object> params = new HashMap<>();
        params.put("permit", permit);
        return this.getList(sql, params);
    }

    @Override
    public List getScSysDataByPermitAndDate(String permit) {
        String sql = "select  \n" +
                "  (select 电话号码 from 顾客记录汇总 where 顾客编号 = a.顾客编号) as custom_tel,  \n" +
                "  (select 人员姓名 from 用户 where 用户名 = a.销售顾问) as sc,  \n" +
                "  if(b.车架号 like 'IRISTe%', null, b.车架号) as vin,  \n" +
                "  if(b.生产号 like 'IRISTe%', null, b.生产号) as prod_num,  \n" +
                "  if((select 车型详细 from lsh车型 where 车型编号 = a.车型编号) = '',  (select 车型 from lsh车型 where 车型编号 = a.车型编号), (select 车型详细 from lsh车型 where 车型编号 = a.车型编号)) as intent_car,  \n" +
                "  (select  \n" +
                "    CASE  \n" +
                "      WHEN NOT ISNULL(c.`交车日期`) THEN '交车'  \n" +
                "      WHEN (NOT ISNULL(c.`开票时间`) AND ISNULL(c.`退票时间`)) OR (NOT ISNULL(c.`开票时间`) AND NOT ISNULL(c.`退票时间`) AND c.`开票时间`>c.`退票时间`) THEN '开票'  \n" +
                "      WHEN NOT ISNULL(c.`退票时间`) AND d.`订金类型`='订金' AND ISNULL(d.`订单失销日期`) THEN '订金'  \n" +
                "      WHEN NOT ISNULL(c.`合同签约日期`) THEN '合同'  \n" +
                "      WHEN NOT ISNULL(d.`订单审批通过日期`) AND ISNULL(d.`订单失销日期`) AND d.`订金类型`='订金' THEN '订金'  \n" +
                "      WHEN NOT ISNULL(d.`订单审批通过日期`) AND ISNULL(d.`订单失销日期`) AND d.`订金类型`='意向金' THEN '意向金'  \n" +
                "      ELSE ''  \n" +
                "    END  \n" +
                "  from 顾客记录汇总 where 顾客编号 = a.顾客编号) as sales_status,  \n" +
                "  if(d.订金金额 is null, null, cast(d.订单审批通过日期 as date)) as io_dt,  \n" +
                "  if((select 顾客姓名 from 顾客记录汇总 where 顾客编号 = a.顾客编号) = '', null, (select 顾客姓名 from 顾客记录汇总 where 顾客编号 = a.顾客编号)) as order_cus_name,  \n" +
                "  d.订金金额 + ifnull(d.订金加减金额, 0) as io_amount,  \n" +
                "  c.合同签约日期 as contract_signed_dt,  \n" +
                "  cast(c.开票时间 as date) as invoice_dt,  \n" +
                "  if(c.开票时间 is null, null, c.开票客户姓名) as invoice_uname,  \n" +
                "  c.交车日期 as deliver_dt,  \n" +
                "  a.折后价 as price_ti,  \n" +
                "  (select cast(随车内容 as decimal)  from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '保险') as freeby_in,  \n" +
                "  (select cast(随车内容 as decimal)  from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '贴息') as freeby_sub_int,  \n" +
                "  (select cast(随车内容 as unsigned) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = 'A保') as freeby_a,  \n" +
                "  (select cast(随车内容 as unsigned) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = 'B保') as freeby_b,  \n" +
                "  (select 随车内容                   from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '尚银卡') as freeby_si,  \n" +
                "  (select 随车内容                   from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '逸蓝卡') as freeby_bl,  \n" +
                "  (select cast(随车内容 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '购物卡') as freeby_sh,  \n" +
                "  (select cast(随车内容 as decimal) from 报价随车表 where 报价编号 = a.报价编号 and 随车类型 = '经销商' and 随车部门 = '销售部' and 随车名称 = '加油卡') as freeby_oi,  \n" +
                "  (select sum(成本 * 数量) from 报价精品详细表 where 折后价 = 0 and 编号 = a.精品 group by 编号) as freeby_bo,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '保险' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_in,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = 'SSSC' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_sssc,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = 'SD卡' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_sd,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '加油卡' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_oil,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '实物礼品' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_pre,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '服务礼券' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as freeby_is_ti,  \n" +
                "  if((select 随车成本 from 报价随车表 where 报价编号 = a.报价编号 and 随车名称 = '购置税' and 随车类型 = '厂家' and 随车部门 = '厂家') is null, 'N', 'Y') as pur_tax_si,  \n" +
                "  (select sum(折后价 * 数量) from 报价精品详细表 where 折后价 <> 0 and 编号 = a.精品 group by 编号) as bo_price_ti,  \n" +
                "  (select cast(延保折后价 as decimal) from 报价五星延保 where 延保编号 = a.延保编号 and 延保折后价 <> 0) as ex_in_price_ti,  \n" +
                "  (SELECT cast(SUM(bb.费用折后价) as decimal) FROM 报价费用表 aa JOIN 报价费用详细表 bb ON bb.费用编号 = aa.费用编号 WHERE bb.费用名称 = '上牌服务费' and aa.费用编号 = a.费用编号 GROUP BY aa.费用编号) as lic_fee,  \n" +
                "  (SELECT cast(SUM(bb.费用折后价) as decimal) FROM 报价费用表 aa JOIN 报价费用详细表 bb ON bb.费用编号 = aa.费用编号 WHERE bb.费用名称 = '其他服务费' and aa.费用编号 = a.费用编号 GROUP BY aa.费用编号) as oth_fee,  \n" +
                "  (select cast(金融服务费 as decimal) from 报价金融表 where 金融编号 = a.金融) as fin_fee  \n" +
                "from 报价单表 a  \n" +
                "join vw_max_quoted_code ag on a.报价编号 = ag.报价编号  \n" +
                "left join 报价订单表 d on a.订单编号 = d.订单编号  \n" +
                "left join lsh库存表 b on a.库存编号 = b.库存编号  \n" +
                "left join 报价交车表 c on a.交车 = c.编号  \n" +
                "where a.许可 = :permit  \n" +
                "and (  \n" +
                "  ((date_format(cast(d.订单审批通过日期 as date), '%Y-%m-%d') <= current_date() and date_format(cast(d.订单审批通过日期 as date), '%Y-%m-%d') >= date_format(concat(year(current_date()), '-', month(current_date()), '-01'), '%Y-%m-%d')) and (d.订单失销日期 is null or date_format(d.订单失销日期, '%Y-%m-%d') < date_format(cast(d.订单审批通过日期 as date), '%Y-%m-%d')))  \n" +
                "  or ((date_format(c.合同签约日期, '%Y-%m-%d') <= current_date() and date_format(c.合同签约日期, '%Y-%m-%d') >= date_format(concat(year(current_date()), '-', month(current_date()), '-01'), '%Y-%m-%d')) and (d.订单失销日期 is null or date_format(d.订单失销日期, '%Y-%m-%d') < date_format(c.合同签约日期, '%Y-%m-%d')))  \n" +
                "  or ((date_format(cast(c.开票时间 as date), '%Y-%m-%d') <= current_date() and date_format(cast(c.开票时间 as date), '%Y-%m-%d') >= date_format(concat(year(current_date()), '-', month(current_date()), '-01'), '%Y-%m-%d')) and (d.订单失销日期 is null or date_format(d.订单失销日期, '%Y-%m-%d') < date_format(cast(c.开票时间 as date), '%Y-%m-%d')))  \n" +
                "  or ((date_format(c.交车日期, '%Y-%m-%d') <= current_date() and date_format(c.交车日期, '%Y-%m-%d') >= date_format(concat(year(current_date()), '-', month(current_date()), '-01'), '%Y-%m-%d')) and (d.订单失销日期 is null or date_format(d.订单失销日期, '%Y-%m-%d') < date_format(c.交车日期, '%Y-%m-%d')))  \n" +
                ")  \n";
        Map<String, Object> params = new HashMap<>();
        params.put("permit", permit);
        return getList(sql, params);
    }

    @Override
    public List<TSalesAdminCheckModel> getScSysDataByImportBatchAndUser(String importBatch, 用户Model user) {
        String sql = "SELECT a.* FROM t_sales_admin_check a \n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit" ;
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",user.get许可());
        return this.getList(sql,params);
    }

    @Override
    public PageData<Map<String, Object>> getContrastResultOfSalesAdmin(RequestBase req, String importBatch, String permit) {
        String sql = "SELECT\n" +
                "\ta.id,\n" +
                "\ta.vin,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.in_stock_dt END AS inStockDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.out_stock_dt END AS outStockDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.re_dt END AS reDt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.order_cus_name END AS orderCusName,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.invoice_uname END AS invoiceUname,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.price_ti END AS priceTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_in END AS freebyIn,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_sub_int END AS freebySubInt,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_a END AS freebyA,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_b END AS freebyB,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_si END AS freebySi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_bl END AS freebyBl,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_sh END AS freebySh,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_oi END AS freebyOi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_in END AS freebyIsIn,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_sssc END AS freebyIsSssc,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_sd END AS freebyIsSd,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_oil END AS freebyIsOil,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_pre END AS freebyIsPre,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.freeby_is_ti END AS freebyIsTi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.pur_tax_si END AS purTaxSi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.re_si END AS reSi,\n" +
                "CASE WHEN a.compare_result = '03' THEN NULL ELSE a.wh_si END AS whSi,\n" +
                "CASE WHEN a.d_in_stock_dt = 0 THEN '1' ELSE '0' END AS dInStockDt,\n" +
                "CASE WHEN a.d_out_stock_dt = 0 THEN '1' ELSE '0' END AS dOutStockDt,\n" +
                "CASE WHEN a.d_re_dt = 0 THEN '1' ELSE '0' END AS dReDt,\n" +
                "CASE WHEN a.d_order_cus_name = 0 THEN '1' ELSE '0' END AS dOrderCusName,\n" +
                "CASE WHEN a.d_invoice_uname = 0 THEN '1' ELSE '0' END AS dInvoiceUname,\n" +
                "CASE WHEN a.d_price_ti = 0 THEN '1' ELSE '0' END AS dPriceTi,\n" +
                "CASE WHEN a.d_freeby_in = 0 THEN '1' ELSE '0' END AS dFreebyIn,\n" +
                "CASE WHEN a.d_freeby_sub_int = 0 THEN '1' ELSE '0' END AS dFreebySubInt,\n" +
                "CASE WHEN a.d_freeby_a = 0 THEN '1' ELSE '0' END AS dFreebyA,\n" +
                "CASE WHEN a.d_freeby_b = 0 THEN '1' ELSE '0' END AS dFreebyB,\n" +
                "CASE WHEN a.d_freeby_si = 0 THEN '1' ELSE '0' END AS dFreebySi,\n" +
                "CASE WHEN a.d_freeby_bl = 0 THEN '1' ELSE '0' END AS dFreebyBl,\n" +
                "CASE WHEN a.d_freeby_sh = 0 THEN '1' ELSE '0' END AS dFreebySh,\n" +
                "CASE WHEN a.d_freeby_oi = 0 THEN '1' ELSE '0' END AS dFreebyOi,\n" +
                "CASE WHEN a.d_freeby_is_in = 0 THEN '1' ELSE '0' END AS dFreebyIsIn,\n" +
                "CASE WHEN a.d_freeby_is_sssc = 0 THEN '1' ELSE '0' END AS dFreebyIsSssc,\n" +
                "CASE WHEN a.d_freeby_is_sd = 0 THEN '1' ELSE '0' END AS dFreebyIsSd,\n" +
                "CASE WHEN a.d_freeby_is_oil = 0 THEN '1' ELSE '0' END AS dFreebyIsOil,\n" +
                "CASE WHEN a.d_freeby_is_pre = 0 THEN '1' ELSE '0' END AS dFreebyIsPre,\n" +
                "CASE WHEN a.d_freeby_is_ti = 0 THEN '1' ELSE '0' END AS dFreebyIsTi,\n" +
                "CASE WHEN a.d_pur_tax_si = 0 THEN '1' ELSE '0' END AS dPurTaxSi,\n" +
                "CASE WHEN a.d_re_si = 0 THEN '1' ELSE '0' END AS dReSi,\n" +
                "CASE WHEN a.d_wh_si = 0 THEN '1' ELSE '0' END AS dWhSi,\n" +
                "\ta.compare_result AS compareResult,\n" +
                "\ta.total_score AS totalScore\n" +
                "FROM t_sales_admin_check a\n" +
                "LEFT JOIN 用户 b ON a.import_user = b.`用户名`\n" +
                "WHERE a.import_batch = :importBatch\n" +
                "AND a.permit = :permit\n" +
                "AND a.compare_result <> '04'" ;
//                "AND b.角色 = :角色";
        Map<String,Object> params = new HashMap<>();
        params.put("importBatch",importBatch);
        params.put("permit",permit);
//        params.put("角色","销售行政");
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }

    @Override
    public PageData getRankingOfSalesAdmin(DataCheckService.RankingRequest req) {
        String sql = "SELECT cast(@rowNum \\:= @rowNum + 1 AS DECIMAL(3,0))AS ranking, " +
                "  f.permit, " +
                "  f.`经销商名` AS distributor, " +
                "  f.`大区`, " +
                "  IFNULL(score,'0') AS score " +
                "FROM ( " +
                "  SELECT  " +
                "  @rowNum \\:= 0 AS ranking, " +
                "  a.permit, " +
                "  b.`经销商名`, " +
                "  b.`大区`, " +
                "  cast(avg(a.total_score) AS DECIMAL(5,2)) score  " +
                "  FROM  " +
                "    ( " +
                "     SELECT * FROM t_sales_admin_check WHERE cast(import_batch AS date) >= cast(:startBatch AS date) AND cast(import_batch as date) <= cast(:endBatch AS date)  " +
                "     ) a " +
                "LEFT JOIN `lsh经销商表` b ON a.permit = b.`许可_BMBS` " +
                "GROUP BY a.permit " +
                "HAVING b.`大区` = ( " +
                "     SELECT c.大区 from `lsh经销商表` c WHERE c.`许可_BMBS` = :permit)          " +
                "ORDER BY AVG(a.total_score) DESC  " +
                ") f";

        Map<String,Object> params = new HashMap<>();
        params.put("permit",req.permit); //许可
        params.put("startBatch",req.startBatch); // 开始批次
        params.put("endBatch",req.endBatch); //结束批次
        return this.getPage(sql,params,req.getPageSize(),req.getPageIndex());
    }
}
